package cn.com.doone.common.uc.infrastructure.jdbc;

import cn.com.doone.common.uc.domain.oauth.AccessToken;
import cn.com.doone.common.uc.domain.oauth.ClientDetails;
import cn.com.doone.common.uc.domain.rs.OAuthRSRepository;
import cn.com.doone.common.uc.infrastructure.DateUtils;
import cn.com.doone.common.uc.infrastructure.jdbc.AbstractJdbcRepository;
import cn.com.doone.common.uc.infrastructure.jdbc.AccessTokenRowMapper;
import cn.com.doone.common.uc.infrastructure.jdbc.ClientDetailsRowMapper;
import cn.com.doone.common.uc.utils.StringUtils;

import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.stereotype.Repository;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

/**
 * 15-6-13
 *
 * @author Shengzhao Li
 */
@Repository("oauthRSJdbcRepository")
public class OAuthRSJdbcRepository extends AbstractJdbcRepository implements OAuthRSRepository {


    private static ClientDetailsRowMapper clientDetailsRowMapper = new ClientDetailsRowMapper();
    private AccessTokenRowMapper accessTokenRowMapper = new AccessTokenRowMapper();


    @Override
    public AccessToken findAccessTokenByTokenId(String tokenId) {
        final String sql = " select * from oauth_access_token where token_id = ?";
        final List<AccessToken> list = jdbcTemplate.query(sql, accessTokenRowMapper, tokenId);
        return list.isEmpty() ? null : list.get(0);
    }

    @Override
    public ClientDetails findClientDetailsByClientIdAndResourceIds(String clientId, String resourceIds) {
        final String sql = " select APP_CODE AS client_id,APP_SECRET AS client_secret,APP_NAME AS client_name,APP_URI AS client_uri,APP_ICON_URI AS client_icon_uri," +
				" RESOURCE_IDS AS resource_ids,SCOPE AS scope,GRANT_TYPES AS grant_types,REDIRECT_URI AS redirect_uri,ROLES AS roles,ACCESS_TOKEN_VALIDITY AS access_token_validity," +
				" REFRESH_TOKEN_VALIDITY AS refresh_token_validity,DESCRIPTION AS description,CREATE_TIME AS create_time,ARCHIVED AS archived,TRUSTED AS trusted" +
				" from app_info where ARCHIVED = 0 and APP_CODE = ? and RESOURCE_IDS = ? ";
        final List<ClientDetails> list = jdbcTemplate.query(sql, clientDetailsRowMapper, clientId, resourceIds);
        return list.isEmpty() ? null : list.get(0);
    }
    
    public List<Map<String, Object>> getTenantInfo(String username) {
    	final String sql = " SELECT" +
    							" DISTINCT(user_group.GROUP_CODE) AS GROUP_CODE" +
    						" FROM" +
    							" user_info, user_group_member, user_group" +
    						" WHERE" +
    							" user_info.USER_INFO_ID = user_group_member.USER_INFO_ID" +
    						" AND" +
    							" user_group_member.USER_GROUP_ID = user_group.USER_GROUP_ID" +
    						" AND" +
    							" user_info.USER_ACCOUNT = ?" +
    						" AND" +
    							" user_group.`STATUS` = '1'" +
					    	" AND" +
								" user_group_member.`STATUS` = '1'";
		final List<Map<String, Object>> list = jdbcTemplate.queryForList(sql, username);
    	return list;
    }
    
    public Map<String, Object> getTenantCode(String groupCode) {
    	final String sql = " SELECT" +
    							" TENANT_CODE,SHIELD_COLUMN" +
    						" FROM" +
    							" user_group" +
    						" WHERE" +
    							" GROUP_CODE = ?";
    	Map<String, Object> map = jdbcTemplate.queryForMap(sql, groupCode);
    	return map;
    }

	@Override
	public Map<String, Object> getUserInfo(String username) {
		
		final String sql = " SELECT" +
								" ui.USER_INFO_ID AS userInfoId," +
								" ui.DOONE_CODE AS dooneCode," +
								" ui.nickname," +
								" ui.realname," +
								" ui.sex," +
								" ui.USER_ACCOUNT AS username," +
								" ui.email," +
								" ui.mobile_phone AS mobilePhone," +
								" uie.birthday,"+
								" uie.HEAD_PORTRAIT AS headPortrait" +
							" FROM" +
								" user_info ui left join user_info_expand uie on ui.user_info_id=uie.user_info_id" +
							" WHERE" +
								" ui.USER_ACCOUNT = ?";
		
		final List<Map<String, Object>> list = jdbcTemplate.queryForList(sql, username); 
    	return list.isEmpty() ? null : list.get(0);
		
	}

	@Override
	public Map<String, Object> getUserInfoExtend(int userInfoId) {
		final String sql = " SELECT" +
								" USER_INFO_ID AS userInfoId," +
								" DOONE_CODE AS dooneCode," +
								" HEAD_PORTRAIT AS headPortrait" +
							" FROM" +
								" user_info_expand" + 
							" WHERE " +
								" USER_INFO_ID = ?";
		final List<Map<String, Object>> list = jdbcTemplate.queryForList(sql, userInfoId); 
    	return list.isEmpty() ? null : list.get(0);
	}

	@Override
	public int saveUserInfoExtend(final Map map) {
		final String sql = " insert into user_info_expand(USER_INFO_ID, DOONE_CODE, HEAD_PORTRAIT) values (?,?,?)";
        return jdbcTemplate.update(sql, new PreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps) throws SQLException {
                ps.setInt(1, (Integer) map.get("userInfoId"));
                ps.setInt(2, (Integer) map.get("dooneCode"));
                ps.setString(3, (String) map.get("headPortrait"));
            }
        });
	}

	@Override
	public int updateUserInfoExtend(final Map map) {
		final String sql = "update user_info_expand set HEAD_PORTRAIT = ? where USER_INFO_ID = ?";
		
    	return jdbcTemplate.update(sql, new PreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps) throws SQLException {
            	ps.setString(1, (String) map.get("headPortrait"));
                ps.setInt(2, (Integer) map.get("userInfoId"));
            }
        });
	}

	@Override
	public int updateUserInfo(final Map map) {
		
		String sql = "UPDATE user_info SET"+ 
						       " NICKNAME = ?,"+ 
						       " SEX = ?,"+ 
						       " EMAIL = ?," +
						       " UPDATE_TIME = NOW()"+ 
						   " WHERE"+ 
							   " USER_ACCOUNT = ?";
		
    	return jdbcTemplate.update(sql, new PreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps) throws SQLException {
            	ps.setString(1, (String) map.get("nickname"));
                ps.setString(2, (String) map.get("sex"));
                ps.setString(3, (String) map.get("email"));
                ps.setString(4, (String) map.get("userAccount"));
            }
        });
	}

	@Override
	public Map<String, Object> queryInfoByToken(Map<String, Object> paramsMap) {
		String sql = "select username from oauth_access_token where token_id=? and client_id=?";
		List<Map<String,Object>> list = jdbcTemplate.queryForList(sql, paramsMap.get("token").toString(),paramsMap.get("clientId").toString());
		return list.isEmpty()?null:list.get(0);
	}

	@Override
	public Map<String, Object> queryAppInfoByClientId(String clientId) {
		String sql = "select * from app_info where app_code=?";
		List<Map<String,Object>> list = jdbcTemplate.queryForList(sql, clientId);
		return list.isEmpty()?null:list.get(0);
	}

	@Override
	public Map<String, Object> queryUserInfoByPhone(String mobilePhone) {
		String sql = "select user_account as username from user_info where mobile_phone = ? and status = 1";
		List<Map<String,Object>> list = jdbcTemplate.queryForList(sql, mobilePhone);
		return list.isEmpty()?null:list.get(0);
	}

	@Override
	public List<Map<String, Object>> getGroupPostTypeListByUserInfoId(String userInfoId) {
		final String sql = "select "
				+" t3.USER_GROUP_ID AS userGroupId ,"
				+" t3.POST_TYPE AS postType"
			+" FROM"
				+" user_group_member t1"
				+" LEFT JOIN user_info t2 ON t1.USER_INFO_ID = t2.USER_INFO_ID"
				+" LEFT JOIN user_group t3 ON t1.USER_GROUP_ID = t3.USER_GROUP_ID"
			+" WHERE t1.USER_INFO_ID =?";
		return jdbcTemplate.queryForList(sql, userInfoId);
	}
}
